﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace cs_exp7_sql
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        MySqlDataAdapter mda; //适配器变量
        DataSet ds;  //临时数据集
        MySqlConnection conn; //连接数据库对象

        private void print_in_dataGridView()
        {
            MySqlCommand mycom = conn.CreateCommand();
            mycom.CommandText = "SELECT * FROM student ; ";
            MySqlDataAdapter adap = new MySqlDataAdapter(mycom);
            DataSet ds = new DataSet();
            adap.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0].DefaultView;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string M_str_sqlcon = "server=localhost;user id=ZuiYi;password=rongxiang;database=demo_03";                                                                                              //创建数据库连接对象
            conn = new MySqlConnection(M_str_sqlcon);
            try
            {
                conn.Open();
                MessageBox.Show("数据库连接成功！");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string table_name = textBox1.Text.Trim();
            if (mda == null || ds == null)
            {
                MessageBox.Show("请先从数据库中导入数据");
                return;
            }
            try
            {
                string msg = "确定要添加此条数据吗？";
                if (1 == (int)MessageBox.Show(msg, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation))
                {
                    MySqlCommandBuilder builder = new MySqlCommandBuilder(mda);
                    mda.Update(ds, table_name);
                    MessageBox.Show("添加成功！", "提示");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), "错误信息");
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            string table_name = textBox1.Text.Trim();
            if (mda == null || ds == null)
            {
                MessageBox.Show("请先从数据库中导入数据");
                return;
            }
            try
            {
                string msg = "确定要修改吗？";
                if (1 == (int)MessageBox.Show(msg, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation))
                {
                    MySqlCommandBuilder builder = new MySqlCommandBuilder(mda);
                    mda.Update(ds, table_name);
                    MessageBox.Show("修改成功！", "提示");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), "错误信息");
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            try
            {
                conn.Open();
                string select_id = textBox4.Text;
                string delete_by_id = "delete from Student where s_id = " + "\"" + select_id + "\"";
                MySqlCommand cmd = new MySqlCommand(delete_by_id, conn);
                cmd.ExecuteNonQuery();
            }
            catch { MessageBox.Show("请正确选择行!", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error); }
            finally { conn.Dispose(); }
            print_in_dataGridView();
        }

        private void button5_Click(object sender, EventArgs e)
        {
            string table_name = textBox1.Text.Trim();
            if (table_name == null && (table_name != "教师") && (table_name != "学生"))
            {
                MessageBox.Show("请填写正确的表格名！");
            }
            else
            {
                string sql = "select * from " + table_name;
                mda = new MySqlDataAdapter(sql, conn);
                ds = new DataSet();
                mda.Fill(ds, table_name);
                dataGridView1.DataSource = ds.Tables[table_name];
                conn.Close();

            }
        }

    }
}
